using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class SSRFANVDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procSSRFANV_add]";
        private string _strSPUpdateName = "dbo.[procSSRFANV_update]";
        private string _strSPDeleteName = "dbo.[procSSRFANV_delete]";
        private string _strSPGetName = "dbo.[procSSRFANV_get]";
        private string _strSPGetCategoryName = "dbo.[sp_procSSRFANV_getcategory]";
        private string _strSPGetAllName = "dbo.[sp_procSSRFANV_getsundb]";
        private string _strSPGetPages = "dbo.[procSSRFANV_getpaged]";
        private string _strSPIsExist = "dbo.[procSSRFANV_isexist]";
        private string _strTableName = "SSRFANV";
        private string _strSPGetTransferOutName = "dbo.[procSSRFANV_gettransferout]";
        #endregion Local Variable

        #region Method
        public SSRFANVInfo Get(
            String SUN_DB,
            String CATEGORY,
            String CODE
        , ref string sErr)
        {
            SSRFANVInfo objEntr = new SSRFANVInfo();
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPGetName;
            AddParameter("SUN_DB", SUN_DB);
            AddParameter("CATEGORY", CATEGORY);
            AddParameter("CODE", CODE);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (SSRFANVInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            SSRFANVInfo result = new SSRFANVInfo();
            result.SUN_DB = (dt.Rows[i]["SUN_DB"] == DBNull.Value ? "" : (String)dt.Rows[i]["SUN_DB"]);
            result.CATEGORY = (dt.Rows[i]["CATEGORY"] == DBNull.Value ? "" : (String)dt.Rows[i]["CATEGORY"]);
            result.CODE = (dt.Rows[i]["CODE"] == DBNull.Value ? "" : (String)dt.Rows[i]["CODE"]);
            result.LOOKUP = (dt.Rows[i]["LOOKUP"] == DBNull.Value ? "" : (String)dt.Rows[i]["LOOKUP"]);
            result.UPDATED = (dt.Rows[i]["UPDATED"] == DBNull.Value ? "" : (String)dt.Rows[i]["UPDATED"]);
            result.NAME = (dt.Rows[i]["NAME"] == DBNull.Value ? "" : (String)dt.Rows[i]["NAME"]);
            result.PROHB_POST = (dt.Rows[i]["PROHB_POST"] == DBNull.Value ? "" : (String)dt.Rows[i]["PROHB_POST"]);
            result.BUDGET_CHECK = (dt.Rows[i]["BUDGET_CHECK"] == DBNull.Value ? "" : (String)dt.Rows[i]["BUDGET_CHECK"]);
            result.BUDGET_STOP = (dt.Rows[i]["BUDGET_STOP"] == DBNull.Value ? "" : (String)dt.Rows[i]["BUDGET_STOP"]);
            result.DATA_1 = (dt.Rows[i]["DATA_1"] == DBNull.Value ? "" : (String)dt.Rows[i]["DATA_1"]);
            result.NAME2 = (dt.Rows[i]["NAME2"] == DBNull.Value ? "" : (String)dt.Rows[i]["NAME2"]);

            return result;
        }

        public DataTable GetAll(string dtb, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            AddParameter("SUN_DB", dtb);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(SSRFANVInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPInsertName;
            AddParameter("SUN_DB", objEntr.SUN_DB);
            AddParameter("CATEGORY", objEntr.CATEGORY);
            AddParameter("CODE", objEntr.CODE);
            AddParameter("LOOKUP", objEntr.LOOKUP);
            AddParameter("UPDATED", objEntr.UPDATED);
            AddParameter("NAME", objEntr.NAME);
            AddParameter("PROHB_POST", objEntr.PROHB_POST);
            AddParameter("BUDGET_CHECK", objEntr.BUDGET_CHECK);
            AddParameter("BUDGET_STOP", objEntr.BUDGET_STOP);
            AddParameter("DATA_1", objEntr.DATA_1);
            AddParameter("NAME2", objEntr.NAME2);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = command.ExecuteScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(SSRFANVInfo objEntr)
        {

            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPUpdateName;
            AddParameter("SUN_DB", objEntr.SUN_DB);
            AddParameter("CATEGORY", objEntr.CATEGORY);
            AddParameter("CODE", objEntr.CODE);
            AddParameter("LOOKUP", objEntr.LOOKUP);
            AddParameter("UPDATED", objEntr.UPDATED);
            AddParameter("NAME", objEntr.NAME);
            AddParameter("PROHB_POST", objEntr.PROHB_POST);
            AddParameter("BUDGET_CHECK", objEntr.BUDGET_CHECK);
            AddParameter("BUDGET_STOP", objEntr.BUDGET_STOP);
            AddParameter("DATA_1", objEntr.DATA_1);
            AddParameter("NAME2", objEntr.NAME2);

            string sErr = "";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
            String SUN_DB,
            String CATEGORY,
            String CODE
        )
        {
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPDeleteName;
            AddParameter("SUN_DB", SUN_DB);
            AddParameter("CATEGORY", CATEGORY);
            AddParameter("CODE", CODE);

            string sErr = "";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(SSRFANVInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPGetPages;
            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
            String SUN_DB,
            String CATEGORY,
            String CODE
        )
        {
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPIsExist;
            AddParameter("SUN_DB", SUN_DB);
            AddParameter("CATEGORY", CATEGORY);
            AddParameter("CODE", CODE);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
        public DataTable GetCategory_SSRFANV(string SUN_DB, string CATEGORY, ref string sErr)
        {
            connect();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = _strSPGetCategoryName;
            AddParameter("SUN_DB", SUN_DB);
            AddParameter("CATEGORY", CATEGORY);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        private string CreateWhereClause(SSRFANVInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where columnName " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method




        public DataTable GetTransferOut(string dtb, string from, string to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("SUN_DB", dtb);
            AddParameter("CATEGORY_FROM", from);
            AddParameter("CATEGORY_TO", to);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
